package com.wnzt.baszh.listener;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.wnzt.baszh.entity.THisViewMain;
import com.wnzt.baszh.entity.THisViewMx;
import com.wnzt.baszh.entity.Tparam;
import com.wnzt.baszh.util.common.ObjectMapper;

public class StartupListener  implements ServletContextListener{
    private static ApplicationContext context;  
    private static JdbcTemplate jdbcTemplate;
    
    public static List<THisViewMain> hisViewMainBfRz = new ArrayList<THisViewMain>();
    public static List<THisViewMx> hisViewMxBfRz = new ArrayList<THisViewMx>();
    public static Map<String,THisViewMain> hisViewMainMap = new HashMap<String, THisViewMain>();
    public static Map<String,List<String>> hisViewFields = new HashMap<String, List<String>>();
    public static Map<String,List<String>> hisViewTypes = new HashMap<String, List<String>>();
    
    public static String hisdbtype = "";
    public static String hisdbdriver = "";
    public static String hisdburl = "";
    public static String hisdbuser = "";
    public static String hisdbpsw = "";
    
    
    private static Map<String,Tparam> map_Tparam = new HashMap<String, Tparam>();

    public void contextInitialized(ServletContextEvent sce) {
        context = this.getContext();
        jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
        this.loadHisView();
        this.loadSysParam();
    }

    public void contextDestroyed(ServletContextEvent sce) {
        // TODO Auto-generated method stub
    }

    public static ApplicationContext getContext(){
        if(context ==null){
            context = new ClassPathXmlApplicationContext("applicationContext*.xml");
        }
        return context;
    }
    
    /**
     * 初始化表配置信息
     * @return
     */
    @SuppressWarnings({ "unchecked" })
    private static void loadHisView(){
        hisViewMainBfRz.clear();
        hisViewMxBfRz.clear();
        hisViewFields.clear();
        hisViewTypes.clear();
        String sqlmain = "select * from thisviewmain t where t.fviewflag='21'";
        String sqlmx = "select * from thisviewmx m,thisviewmain t where m.ftablename=t.ftablename and t.fviewflag='21'";
        hisViewMainBfRz = jdbcTemplate.query(sqlmain,new ObjectMapper(THisViewMain.class));
        hisViewMxBfRz = jdbcTemplate.query(sqlmx,new ObjectMapper(THisViewMx.class));
        for(int i=0; i<hisViewMainBfRz.size(); i++){
            THisViewMain viewmain = hisViewMainBfRz.get(i);
            hisViewFields.put(viewmain.getFtablename().trim(), new ArrayList<String>());
            hisViewTypes.put(viewmain.getFtablename().trim(), new ArrayList<String>());
            hisViewMainMap.put(viewmain.getFtablename().trim(), viewmain);
        }
        for(int i=0; i<hisViewMxBfRz.size(); i++){
            THisViewMx viewmx = hisViewMxBfRz.get(i);
            hisViewFields.get(viewmx.getFtablename().trim()).add(viewmx.getFviewfield().trim());
            hisViewTypes.get(viewmx.getFtablename().trim()).add(viewmx.getFfieldtype().trim());
        }
    }
    
    private static void loadSysParam() {
        String sql = "select * from tparam";
        List<Tparam> params = jdbcTemplate.query(sql, new ObjectMapper(Tparam.class));
        for(int i=0; i<params.size(); i++){
            Tparam param = params.get(i);
            if(param.getFcode().trim().equalsIgnoreCase("hisDbType")){
                hisdbtype = param.getFvalue().trim();
            }else if(param.getFcode().trim().equalsIgnoreCase("hisDbDriver")){
                hisdbdriver = param.getFvalue().trim();
            }else if(param.getFcode().trim().equalsIgnoreCase("hisDbUrl")){
                hisdburl = param.getFvalue().trim();
            }else if(param.getFcode().trim().equalsIgnoreCase("hisDbUser")){
                hisdbuser = param.getFvalue().trim();
            }else if(param.getFcode().trim().equalsIgnoreCase("hisDbPsw")){
                hisdbpsw = param.getFvalue().trim();
            }
        }
    }
    
    /**
     * 判断查询结果集中是否存在某列
     * 
     * @param rs
     *            查询结果集
     * @param columnName
     *            列名
     * @return true 存在; false 不存咋
     */
    public static boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0) {
                return true;
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }
    
    private static boolean isHisBasyIsExist(String tablename, String uniqueWhereSql)
            throws Exception {
        if (uniqueWhereSql == null || "".equals(uniqueWhereSql)) {
            return false;
        }

        String sql = "select 1 from " + tablename + " where " + uniqueWhereSql;
        int count = getSqlCount(sql);
        return count <= 0 ? false : true;
    }
    
    /**
     * 判断记录是否存在
     * @param tablename
     * @param uniqueWhereSql
     * @return
     * @throws Exception
     */
    private static boolean isRecordExist(String tablename, String uniqueWhereSql)
            throws Exception {
        if (uniqueWhereSql == null || "".equals(uniqueWhereSql)) {
            return false;
        }

        String sql = "select count(1) from " + tablename + " where " + uniqueWhereSql;
        int count = getSqlCount(sql);
        return count <= 0 ? false : true;
    }
    
    /**
     * 查询记录数
     * @param sql
     * @return
     * @throws Exception
     */
    public static int getSqlCount(String sql) throws Exception {
        int result = 0;
        try {
            result = jdbcTemplate.queryForInt(sql);
        } catch (Exception e) {
            throw new Exception("执行查询语句 " + sql + " 出错：" + e.getMessage());
        }

        return result;
    }
    
    private boolean delExistRecord(String tablename, String uniqueWhereSql) {
        try {
            String sql = "delete from " + tablename + " where " + uniqueWhereSql;
            jdbcTemplate.execute(sql);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
    /**
     * where条件填充
     * @param rs
     * @param uniquefields
     * @param uniquefieldtypes
     * @param delete
     * @return
     * @throws DaoException
     */
    public static String fillWhereSql(ResultSet rs, String[] uniquefields,
            String[] uniquefieldtypes, boolean delete) throws Exception {
        String whereSql = "";
        try {
            for (int i = 0; i < uniquefields.length; i++) {
                String column = uniquefields[i];
                String fieldType = uniquefieldtypes[i];
                if (!isExistColumn(rs, column)) {// 视图中不存在字段则跳过
                    continue;
                }
                if (fieldType.toLowerCase().startsWith("varchar")) {// 处理字符串类型
                    if (rs.getString(column) == null || "".equals(rs.getString(column).trim())) {
                        //throw new Exception(column+"字段不能为空");
                        throw new Exception(column+"字段不能为空");
                        //whereSql += " " + column + "='' and";
                    } else {
                        whereSql += " " + column + "='" + rs.getString(column).trim() + "' and";
                    }
                } else if (fieldType.toLowerCase().startsWith("int")
                        || fieldType.toLowerCase().startsWith("tinyint")
                        || fieldType.toLowerCase().startsWith("bigint")
                        || fieldType.toLowerCase().startsWith("numeric")
                        || fieldType.toLowerCase().startsWith("bit")
                        || fieldType.toLowerCase().startsWith("float")) {// 处理数字类型
                    if (rs.getString(column) == null || "".equals(rs.getString(column).trim())) {
                        throw new Exception(column+"字段不能为空");
                        //whereSql += " " + column + "=null and";
                    } else {
                        whereSql += " " + column + "=" + rs.getString(column).trim() + " and";
                    }
                } else if (fieldType.toLowerCase().startsWith("date")) {// 日期处理，如果是空值则设空，否则填入日期
                    System.out.println(rs.getDate(column));
                    if (rs.getString(column) == null || "".equals(rs.getString(column).trim())) {
                        throw new Exception(column+"字段不能为空");
                        //whereSql += " " + column + "=null and";
                    } else {
                        if (delete) {
                            String columnval = rs.getString(column).trim();
                            columnval = columnval.substring(0, 10);
                            whereSql += " CONVERT(varchar(100)," + column + ",23)='" + columnval + "' and";
                        } else {
                            whereSql += " " + column + "='" + rs.getString(column).trim() + "' and";
                        }
                    }
                }
            }
            if (whereSql.endsWith("and")) {
                whereSql = whereSql.substring(0, whereSql.length() - 3);
            }
            return whereSql;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("更新数据前填充表格唯一键出错");
        }
    }
    
    /**
     * @param pattern 模式 add 添加  override 覆盖
     * @return
     * @throws DaoException
     */
    public static boolean importHisSyMs(String tablename, ResultSet rs,
            String[] columns, String[] fieldtypes, String[] uniquefields,
            String[] uniquefieldtypes, String pattern) throws Exception {
        boolean flag = true;
        try{
            ResultSetMetaData rsmd = rs.getMetaData();
            //while (rs.next()) {
                String fields = "";
                String values = "";
                String setValues = "";
                System.out.println("总共有" +rsmd.getColumnCount() + "列....");
                for (int i = 0; i < columns.length; i++) {
                    String column = columns[i];
                    String fieldType = fieldtypes[i];
                    // System.out.println("["+i+"]"+column+":"+fieldType);
                    if (!isExistColumn(rs, column)) {// 视图中不存在字段则跳过
                        continue;
                    }
                    if (fieldType.toLowerCase().trim().startsWith("varchar")) {// 处理字符串类型
                        if (rs.getString(column) == null || "".equals(rs.getString(column).trim())) {
                            values += "'',";
                            // setValues += column + "='',";
                        } else {
                            values += "'" + rs.getString(column).trim() + "',";
                            setValues += column + "='" + rs.getString(column).trim() + "',";
                        }
                    } else if (fieldType.toLowerCase().trim().startsWith("int")
                            || fieldType.toLowerCase().trim().startsWith("tinyint")
                            || fieldType.toLowerCase().trim().startsWith("bigint")
                            || fieldType.toLowerCase().trim().startsWith("numeric")
                            || fieldType.toLowerCase().trim().startsWith("bit")
                            || fieldType.toLowerCase().trim().startsWith("float")) {// 处理数字类型
                        if (rs.getString(column) == null || "".equals(rs.getString(column).trim())) {
                            values += "null,";
                            // setValues += column + "=null,";
                        } else {
                            values += rs.getString(column).trim() + ",";
                            setValues += column + "=" + rs.getString(column).trim() + ",";
                        }
                    } else if (fieldType.toLowerCase().trim().startsWith("date")) {// 日期处理，如果是空值则设空，否则填入日期
                        if (rs.getString(column) == null || "".equals(rs.getString(column).trim())) {
                            values += null + ",";
                            // setValues += column + "=null,";
                        } else {
                            values += "'" + rs.getString(column).trim() + "',";
                            setValues += column + "='" + rs.getString(column).trim() + "',";
                        }
                    }
                    fields += column + ",";
                }
                if (values.endsWith(",")) {
                    values = values.substring(0, values.length() - 1);
                }
                if (fields.endsWith(",")) {
                    fields = fields.substring(0, fields.length() - 1);
                }
                if (setValues.endsWith(",")) {
                    setValues = setValues.substring(0, setValues.length() - 1);
                }
                boolean isExist = isRecordExist(tablename,fillWhereSql(rs, uniquefields, uniquefieldtypes, false));
                if(isExist){
                    if("override".equalsIgnoreCase(pattern)){
                        String updatesql = "update " + tablename + " set " + setValues + " where " + fillWhereSql(rs, uniquefields, uniquefieldtypes, false);
                        jdbcTemplate.execute(updatesql);
                    }
                }else{
                    if("add".equalsIgnoreCase(pattern)){
                        String insertsql = "insert into " + tablename + "(" + fields + ") values (" + values + ")";
                        jdbcTemplate.execute(insertsql);
                    }
                }
            //}
        }catch (Exception e) {
            e.printStackTrace();
            throw new Exception("导入数据出错:"+e.getMessage(),e.getCause());
        }
        return flag;
    }
    
    /**
     * 获取报表字段数组
     * @param ftablename
     * @return
     */
    public static String[] getHisColumnProperties(String ftablename){
        String[] array = null;
        List<String> fieldlist = hisViewFields.get(ftablename);
        array = fieldlist.toArray(new String[fieldlist.size()]);
        return array;
    }
    
    public static String[] getHisColumnTypes(String ftablename){
        String[] array = null;
        List<String> fieldlist = hisViewTypes.get(ftablename);
        array = fieldlist.toArray(new String[fieldlist.size()]);
        return array;
    }
}
